package com.example.myspring.util;

import com.example.myspring.entity.Student;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.Test;


import java.io.*;
import java.util.ArrayList;

public class ExcelServletUtil {

    @Test
    public void create() throws IOException {

        Workbook wb3 = new HSSFWorkbook();//03工作薄对象
        Sheet sheet3 = wb3.createSheet();//生成工作表对象
        Row row1 = sheet3.createRow(0);//生成行

        Cell cell00 = row1.createCell(0);//生成单元格
        cell00.setCellValue("name");//插入name

        Cell cell01 = row1.createCell(1);//(?????????)??0??1??
        cell01.setCellValue("sex");//??(0,0)??д??sex

        Cell cell02 = row1.createCell(2);//(?????????)??0??2??
        cell02.setCellValue("age");//??(0,0)??д??age

        Cell cell03 = row1.createCell(3);//(?????????)??0??3??
        cell03.setCellValue("address");//??(0,0)??д??address


        FileOutputStream fos = new FileOutputStream("D:\\ExcelData\\" + "asd.xls");
        wb3.write(fos);
        fos.close();

        System.out.println("生成完毕");
    }

    @Test
    public void read() throws IOException {
        FileInputStream fls = new FileInputStream("C:\\Users\\zx\\Desktop\\" + "Student.xlsx");
        ergodic(fls);

    }


    //根据输入流获取工作簿对象遍历第一张表所有行的所有单元格
    public static void ergodic(FileInputStream fls) throws IOException {
        Workbook wb = new XSSFWorkbook(fls);//簿

        Sheet sheet = wb.getSheetAt(0);//表

        Row rowTitle = sheet.getRow(0);//行

        //获取当前表总行数
        int rowCount = sheet.getPhysicalNumberOfRows();
        //遍历全部行
        for (int rowNumber = 0; rowNumber < rowCount; rowNumber++) {
            //拿到当前行数据
            Row rowData = sheet.getRow(rowNumber);
            //非空判断
            if (rowData != null) {
                //拿到当前行总单元格数量
                int cellCount = rowTitle.getPhysicalNumberOfCells();

                //根据当前行数据遍历所有单元格
                for (int cellNumber = 0; cellNumber < cellCount; cellNumber++) {
                    //拿到单元格对象
                    String cellValue = selectCellForRow(rowData, cellNumber);
                    //输出单元格值
                    System.out.println(cellValue);
                }


            }
        }
    }


    //根据行数据拿到单元格数据
    public static String selectCellForRow(Row rowData, int cellNumber) {
        Cell cell = rowData.getCell(cellNumber);
        //非空判断
        String cellValue = "";
        if (cell != null) {
            //判断类型
            cellValue = determine(cell, cellValue);
        }
        return cellValue;
    }


    //根据单元格对象判断单元格类型返回字符串
    public static String determine(Cell cell, String cellValue) {
        switch (cell.getCellType()) {
            case HSSFCell.CELL_TYPE_STRING://字符串类型
                cellValue = cell.getStringCellValue();
                break;
            case HSSFCell.CELL_TYPE_NUMERIC://数字类型
                //判断是否为时间格式
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    //转换
                    cellValue = String.valueOf(cell.getDateCellValue());
                } else {
                    //若不是则为数字格式  为避免数字过大将数字转换为字符串输出
                    cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                    cellValue = cell.getStringCellValue();
                }
                break;
            case HSSFCell.CELL_TYPE_BOOLEAN://boolean类型
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            case HSSFCell.CELL_TYPE_ERROR://ERROR类型
                System.out.println("错误类型");
                break;
        }
        return cellValue;
    }



    //Servlet专用 根据输入流获取excel对象 遍历表格生成Student对象 返回对象集合
    public static ArrayList excelForStudent(InputStream input,ArrayList list) throws IOException {
        //工作簿
        Workbook wb = new XSSFWorkbook(input);
        //工作表
        Sheet sheet = wb.getSheetAt(0);//表
        //首行
        Row rowTitle = sheet.getRow(0);//行
        //获取当前表总行数
        int rowCount = sheet.getPhysicalNumberOfRows();
        //遍历全部行
        for (int rowNumber = 0; rowNumber < rowCount; rowNumber++) {
            //拿到当前行数据
            Row rowData = sheet.getRow(rowNumber);
            //非空判断
            if (rowData != null) {
                //初始化对象
                Student student = new Student();
                //拿到当前行总单元格数量
                int cellCount = rowTitle.getPhysicalNumberOfCells();
                //根据当前行数据遍历当前行单元格
                for (int cellNumber = 0; cellNumber < cellCount; cellNumber++) {
                    //拿到单元格对象
                    String cellValue = ExcelServletUtil.selectCellForRow(rowData, cellNumber);
                    //非空判断
                    if (!cellValue.isEmpty()) {
                        //输出单元格值
                        switch (cellNumber) {
                            case 0:
                                student.setName(cellValue);
                                break;
                            case 1:
                                student.setSex(cellValue);
                                break;
                            case 2:
                                student.setAge(cellValue);
                                break;
                            case 3:
                                student.setAddress(cellValue);
                                break;
                            default:
                                break;
                        }
                    }
                }
                list.add(student);//向集合中添加对象
            }
        }
        return list;
    }
}


